﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

/// <summary>
/// Summary description for ManageBusSchedule
/// </summary>
public class ManageBusSchedule
{
    public ManageBusSchedule()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    public int deleteBusSchedule(int IDSchedule)
    {
        SqlConnection con = ManageConnect.OpenConnection();

        SqlCommand cmd = new SqlCommand("deleteBusSchedule", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@IDSchedule", SqlDbType.Int).Value = IDSchedule;

        SqlParameter result = new SqlParameter("@ReturnValue", SqlDbType.Int);
        result.Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add(result);

        try
        {
            cmd.ExecuteNonQuery();
            int val;
            if (int.TryParse(result.Value.ToString(), out val))
            {
                return val;
            }
            else return -1;
        }
        catch (SqlException ex)
        {
            /* ghi ra file log*/
            return -1;
        }
        finally
        {
            ManageConnect.CloseConnection(con);
        }
    }

    public int updateBusSchedule(int IDSchedule,int IDStation)
    {
        SqlConnection con = ManageConnect.OpenConnection();

        SqlCommand cmd = new SqlCommand("updateBusSchedule", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@IDSchedule", SqlDbType.Int).Value = IDSchedule;
        cmd.Parameters.Add("@IDStation", SqlDbType.NVarChar).Value = IDStation;

        SqlParameter result = new SqlParameter("@ReturnValue", SqlDbType.Int);
        result.Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add(result);

        try
        {
            cmd.ExecuteNonQuery();
            int val;
            if (int.TryParse(result.Value.ToString(), out val))
            {
                return val;
            }
            else return -1;
        }
        catch (SqlException ex)
        {
            /* ghi ra file log*/
            return -1;
        }
        finally
        {
            ManageConnect.CloseConnection(con);
        }
    }
  
    public DataSet searchAllBusScheduleByDirectAndBus(String direction, int id, String search)
    {
        DataSet ds = new DataSet();
        SqlConnection con = ManageConnect.OpenConnection();
        try
        {
            SqlDataAdapter sqlDb = new SqlDataAdapter("searchAllBussSchedule", con);
            sqlDb.SelectCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter param = new SqlParameter("@IDBusTrip", SqlDbType.Int);
            param.Value = id;
            sqlDb.SelectCommand.Parameters.Add(param);

            param = new SqlParameter("@Direction", SqlDbType.NVarChar);
            param.Value = direction;
            sqlDb.SelectCommand.Parameters.Add(param);

            param = new SqlParameter("@STRING_SEARCH", SqlDbType.NVarChar);
            param.Value = search;
            sqlDb.SelectCommand.Parameters.Add(param);

            sqlDb.Fill(ds, "BusSchedule");
        }
        catch (SqlException ex)
        {

        }
        finally
        {
            ManageConnect.CloseConnection(con);
        }

        return ds;
    }

    public DataSet getAllBusScheduleByDirectAndBus(String direction, int id)
    {
        DataSet ds = new DataSet();
        SqlConnection con = ManageConnect.OpenConnection();
        try
        {
            SqlDataAdapter sqlDb = new SqlDataAdapter("getAllBussSchedule", con);
            sqlDb.SelectCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter param = new SqlParameter("@IDBusTrip", SqlDbType.Int);
            param.Value = id;
            sqlDb.SelectCommand.Parameters.Add(param);

            param = new SqlParameter("@Direction", SqlDbType.NVarChar);
            param.Value = direction;
            sqlDb.SelectCommand.Parameters.Add(param);

            sqlDb.Fill(ds, "BusSchedule");
        }
        catch (SqlException ex)
        {

        }
        finally
        {
            ManageConnect.CloseConnection(con);
        }

        return ds;
    }

    public int insertNewBusSchedule(BusSchedule busSchedule)
    {
        SqlConnection con = ManageConnect.OpenConnection();

        SqlCommand cmd = new SqlCommand("inserrNewBusSchedule", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@IDBusTrip", SqlDbType.Int).Value = busSchedule.IDBusTrip;
        cmd.Parameters.Add("@IDStation", SqlDbType.Int).Value = busSchedule.IDStation;
        cmd.Parameters.Add("@OrderSchedule", SqlDbType.Int).Value = busSchedule.OrderSchedule;
        cmd.Parameters.Add("@Direction", SqlDbType.NVarChar).Value = busSchedule.Direction;


        SqlParameter result = new SqlParameter("@ReturnValue", SqlDbType.Int);
        result.Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add(result);

        try
        {
            cmd.ExecuteNonQuery();
            int val;
            if (int.TryParse(result.Value.ToString(), out val))
            {
                return val;
            }
            else return -1;
        }
        catch (SqlException ex)
        {
            /* ghi ra file log*/
            return -1;
        }
        finally
        {
            ManageConnect.CloseConnection(con);
        }
    }

    public DataSet getBusStationNewEst(String direction, int id)
    {
        DataSet ds = new DataSet();
        SqlConnection con = ManageConnect.OpenConnection();
        try
        {
            SqlDataAdapter sqlDb = new SqlDataAdapter("getScheduleNewest", con);
            sqlDb.SelectCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter param = new SqlParameter("@IDBusTrip", SqlDbType.Int);
            param.Value = id;
            sqlDb.SelectCommand.Parameters.Add(param);

            param = new SqlParameter("@Direction", SqlDbType.NVarChar);
            param.Value = direction;
            sqlDb.SelectCommand.Parameters.Add(param);

            sqlDb.Fill(ds, "BusSchedule");
        }
        catch (SqlException ex)
        {

        }
        finally
        {
            ManageConnect.CloseConnection(con);
        }

        return ds;
    }

    public int getScheduleOrderMax(String direction, int id)
    {
        SqlConnection con = ManageConnect.OpenConnection();

        SqlCommand cmd = new SqlCommand("getScheduleOrderMax", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@IDBusTrip", SqlDbType.Int).Value = id;
        cmd.Parameters.Add("@Direction", SqlDbType.NVarChar).Value = direction;

        SqlParameter result = new SqlParameter("@ReturnValue", SqlDbType.Int);
        result.Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add(result);

        try
        {
            cmd.ExecuteNonQuery();
            int val;
            if (int.TryParse(result.Value.ToString(), out val))
            {
                return val;
            }
            else return -1;
        }
        catch (SqlException ex)
        {
            /* ghi ra file log*/
            return -1;
        }
        finally
        {
            ManageConnect.CloseConnection(con);
        }
    }
}